package Models;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import utils.GetConnection;


public class Provider {
	
		private int id;
		private String firstName;
		private String lastName;
		private String phoneNumber;
		private String emailAdress;
		private Date birthDate;
		private String address;
		private String city;
		private String country; 
		
		public Provider() {
			
		}
		
		public Provider(int id, String firstName, String lastName, String phoneNumber, String emailAdress, 
				Date birthDate, String address, String city, String country) {
			this.id = id;
			this.firstName = firstName;
			this.lastName = lastName;
			this.phoneNumber = phoneNumber;
			this.emailAdress = emailAdress;
			this.birthDate = birthDate;
			this.address = address;
			this.city = city;
			this.country = country;
		}
		
		public static List<Provider> LoadSql() {
			return LoadSql("");
		}
		
		public static Provider LoadSql(int id) {
			String sqlWhere =  "id = " + id;
			
			return LoadSql(sqlWhere).get(0);
		}
			
		public static List<Provider> LoadSql(String sqlWhere) {
			int id;
			String firstName = "", lastName = "", phoneNumber = "", emailAdress = "";
			String address = "", city = "", country = "";
			Date birthDate;
			
			List<Provider> providers = new ArrayList<Provider>();
			
			Connection conn = CustomConnection.getConnection();
			Statement stmt = null;
			try {
				try {
					Class.forName("com.mysql.jdbc.Driver");
				}
				catch (Exception e) { System.out.println(e); }

				stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM PROVIDERS" + ((! sqlWhere.equals("")) ? " WHERE " : "") + sqlWhere);
				
				while(rs.next()) {
					id = rs.getInt("id");
					firstName = rs.getString("first_name");
					lastName = rs.getString("last_name");
					phoneNumber = rs.getString("phone_number");
					emailAdress = rs.getString("email");
					birthDate = rs.getDate("birth_date");
					address = rs.getString("address");
					city = rs.getString("city");
					country = rs.getString("country");
					
					providers.add(new Provider(id, firstName, lastName, phoneNumber, emailAdress, birthDate, address, city, country));
				}
			}
			catch (SQLException e) {
				System.out.println(e);
			}
			finally {
				try {
					if(stmt != null) stmt.close();
				} catch (SQLException e) {}
			}
			
			return providers;
		}
		
		public static boolean Save(Provider provider) {
			Connection conn = CustomConnection.getConnection();
			PreparedStatement stmt = null;
			try {
				try {
					Class.forName("com.mysql.jdbc.Driver");
				}
				catch (Exception e) { System.out.println(e); }
				
				

				stmt = conn.prepareStatement("INSERT INTO providers (first_name, last_name, phone_number, email, city, country) VALUES(?,?,?,?,?,?)");
				stmt.setString(1, provider.getFirstName());
				stmt.setString(2, provider.getLastName());
				stmt.setString(3, provider.getPhoneNumber());
				stmt.setString(4, provider.getEmailAdress());
				stmt.setString(5, provider.getCity());
				stmt.setString(6, provider.getCountry());
				
				return stmt.executeUpdate() != 0 ? true : false;
			}
			catch (SQLException e) {
				System.out.println(e);
			}
			finally {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			return false;
		}
		
		
		
		public static void delete(Provider provider) {
			Connection conn = CustomConnection.getConnection();
			
			try {
				try {
					Class.forName("com.mysql.jdbc.Driver");
				}
				catch (Exception e) { System.out.println(e); }
				
				Statement stmt = null;
				
				stmt = conn.createStatement();
				stmt.executeUpdate("DELETE FROM providers WHERE id = " + provider.getId());
			}
			catch (SQLException e) {
				System.out.println(e);
			}
			finally {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		public int getId() {
			return id;
		}

		public void setId(int id) {
			this.id = id;
		}

		public String getName() {
			return firstName;
		}

		public void setName(String name) {
			this.firstName = name;
		}

		public String getSurname() {
			return lastName;
		}

		public void setSurname(String surname) {
			this.lastName = surname;
		}

		public String getPhoneNumber() {
			return phoneNumber;
		}

		public void setPhoneNumber(String phoneNumber) {
			this.phoneNumber = phoneNumber;
		}

		public String getEmailAdress() {
			return emailAdress;
		}

		public void setEmailAdress(String emailAdress) {
			this.emailAdress = emailAdress;
		}

		public String getFirstName() {
			return firstName;
		}

		public void setFirstName(String firstName) {
			this.firstName = firstName;
		}

		public String getLastName() {
			return lastName;
		}

		public void setLastName(String lastName) {
			this.lastName = lastName;
		}

		public Date getBirthDate() {
			return birthDate;
		}

		public void setBirthDate(Date birthDate) {
			this.birthDate = birthDate;
		}

		public String getAddress() {
			return address;
		}

		public void setAddress(String address) {
			this.address = address;
		}

		public String getCity() {
			return city;
		}

		public void setCity(String city) {
			this.city = city;
		}

		public String getCountry() {
			return country;
		}

		public void setCountry(String country) {
			this.country = country;
		}

		public boolean update() {
			String req = "update providers set first_name = ?, last_name = ?, phone_number = ?, email = ?, address = ?, city = ?, country = ? where id = ?";
			PreparedStatement stm = null;
			
			try {
				stm = GetConnection.preparedStatement(req);
				stm.setString(1, firstName);
				stm.setString(2, lastName);
				stm.setString(3, phoneNumber);
				stm.setString(4, emailAdress);
				stm.setString(5, address);
				stm.setString(6, city);
				stm.setString(7, country);
				stm.setInt(8, id);
				if(stm.executeUpdate() != 0)
					return true;
				else
					return false;
			} 
			catch (SQLException e) {e.printStackTrace();}
			finally{
				try { if(stm != null) stm.close(); }
				catch (SQLException e) {}
			}
			
			return false;
		}
}

